﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using jsm.SqlHelper;

namespace dbcaac
{
    public sealed class EntityJiyuanXM : EntityBase
    {
        public string xmId { get; private set; }
        public string xmName { get; private set; }
        public string xmCreateOwner { get; private set; }
        public string xmCreateDate { get; private set; }
        public string xmProf { get; private set; }

        public string pageSize { get; private set; }
        public string pageNumber { get; private set; }

        public override void loadPageData(HttpContext context)
        {
            xmName = context.Request["jyxmName"];
            xmProf = context.Request["jyxmProf"];
            if (string.IsNullOrEmpty(xmProf))
            {
                xmProf = context.Request["zhuanye"];
            }
            xmId = context.Request["id"];
            xmCreateOwner = "1";    // TODO: 从Session中取用户
            if (string.IsNullOrEmpty(xmId)) {
                xmId = context.Request["jyxmId"];
            }
            if (string.IsNullOrEmpty(xmId)) {
                int maxId = (int)SqlServerHelper.ExecuteScalar("SELECT MAX(jyxmId) FROM jiyuanXM");
                xmId = (++maxId).ToString();
            }
            if (!string.IsNullOrEmpty(xmName))
            {
                xmName.Trim();
            }

            pageSize = context.Request["rows"];      // 每页的行数
            pageNumber = context.Request["page"];    // 当前页码
        }

        public override string isValid()
        {

            if (string.IsNullOrEmpty(xmName))
                return "基元项目名称不能为空";
            if (string.IsNullOrEmpty(xmProf))
                return "基元专业不能为空";
            return null;
        }

        public override int getTotal()
        {
            string sql = string.IsNullOrEmpty(xmProf) ?
                            "SELECT count(*) FROM [jiyuanXM]" :
                            string.Format("SELECT count(*) FROM [jiyuanXM] WHERE (jyxmProf = {0})", xmProf);

            return (int)SqlServerHelper.ExecuteScalar(sql);
        }

        protected override int doInsertOperation()
        {
            using (SqlServerHelper helper = new SqlServerHelper())
            {
                helper.Open();
                SqlTransaction transaction = helper.Connection.BeginTransaction();
                helper.Command.Transaction = transaction;
                try
                {
                    // 1、插入数据
                    helper.Command.CommandText = @"INSERT INTO [jiyuanXM] (jyxmId, jyxmName, jyxmCreateOwner, jyxmCreateDate, jyxmProf, 
                                                    jyxmNewFlag, jyxmDeleteFlag, jyxmLogId) VALUES (@jyxmId, @jyxmName, 
                                                    @jyxmCreateOwner, @jyxmCreateDate, @jyxmProf, 1, 0, @jyxmLogId)";
                    Guid logId = Guid.NewGuid();
                    string time = System.DateTime.Now.ToString();

                    helper.AddParameter("@jyxmId", SqlDbType.Int, int.Parse(xmId));
                    helper.AddParameter("@jyxmName", SqlDbType.NVarChar, xmName);
                    helper.AddParameter("@jyxmCreateOwner", SqlDbType.Int, xmCreateOwner);
                    helper.AddParameter("@jyxmCreateDate", SqlDbType.DateTime, time);
                    helper.AddParameter("@jyxmProf", SqlDbType.Int, xmProf);
                    helper.AddParameter("@jyxmLogId", SqlDbType.UniqueIdentifier, logId);

                    int retVal = helper.ExecuteNoneQuery();

                    // 2、写日志
                    helper.Command.CommandText = @"INSERT INTO [jiyuanXMLog] (xmrzLogId, xmrzChangedId, xmrzChangedType, xmrzChangedBy, 
                                                  xmrzChangedDate, xmrzApprovalStatus, xmrzName, xmrzProf) VALUES (@xmrzLogId, @xmrzChangedId, 
                                                  @xmrzChangedType, @xmrzChangedBy, @xmrzChangedDate, @xmrzApprovalStatus, @xmrzName, @xmrzProf)";

                    helper.AddParameter("@xmrzLogId", SqlDbType.UniqueIdentifier, logId);
                    helper.AddParameter("@xmrzChangedId", SqlDbType.Int, int.Parse(xmId));
                    helper.AddParameter("@xmrzChangedType", SqlDbType.VarChar, "NEW");
                    helper.AddParameter("@xmrzChangedBy", SqlDbType.Int, 1);    // TODO: 取用户id
                    helper.AddParameter("@xmrzChangedDate", SqlDbType.DateTime, time);
                    helper.AddParameter("@xmrzApprovalStatus", SqlDbType.VarChar, "UNAPPROVED");

                    helper.AddParameter("@xmrzName", SqlDbType.NVarChar, xmName);
                    helper.AddParameter("@xmrzProf", SqlDbType.Int, xmProf);

                    helper.ExecuteNoneQuery();

                    transaction.Commit();
                    return retVal;
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            } 
        }

        protected override int doDeleteOperation()
        {
            string sqlText = @"INSERT INTO [jiyuanXMLog] (xmrzLogId, xmrzChangedId, xmrzChangedType, xmrzChangedBy, xmrzChangedDate, xmrzApprovalStatus)
                               VALUES (@xmrzLogId, @xmrzChangedId, @xmrzChangedType, @xmrzChangedBy, @xmrzChangedDate, @xmrzApprovalStatus)";

            List<SqlParameter> sqlParams = new List<SqlParameter>();

            sqlParams.Add(new SqlParameter("@xmrzLogId", Guid.NewGuid()));
            sqlParams.Add(new SqlParameter("@xmrzChangedId", int.Parse(xmId)));
            sqlParams.Add(new SqlParameter("@xmrzChangedType", "DELETE"));
            sqlParams.Add(new SqlParameter("@xmrzChangedBy", "1"));    // TODO: 取用户id
            sqlParams.Add(new SqlParameter("@xmrzChangedDate", System.DateTime.Now.ToString()));
            sqlParams.Add(new SqlParameter("@xmrzApprovalStatus", "UNAPPROVED"));

            return SqlServerHelper.ExecuteNonQuery(sqlText, sqlParams.ToArray());
         }

        protected override int doUpdateOperation()
        {
            string sqlText = @"INSERT INTO [jiyuanXMLog] (xmrzLogId, xmrzChangedId, xmrzChangedType, xmrzChangedBy, xmrzChangedDate, xmrzApprovalStatus,
                                xmrzName, xmrzProf) VALUES (@xmrzLogId, @xmrzChangedId, @xmrzChangedType, @xmrzChangedBy, @xmrzChangedDate, @xmrzApprovalStatus,
                                @xmrzName, @xmrzProf)";

            List<SqlParameter> sqlParams = new List<SqlParameter>();

            sqlParams.Add(new SqlParameter("@xmrzLogId", Guid.NewGuid()));
            sqlParams.Add(new SqlParameter("@xmrzChangedId", int.Parse(xmId)));
            sqlParams.Add(new SqlParameter("@xmrzChangedType", "UPDATE"));
            sqlParams.Add(new SqlParameter("@xmrzChangedBy", "1"));    // TODO: 取用户id
            sqlParams.Add(new SqlParameter("@xmrzChangedDate", System.DateTime.Now.ToString()));
            sqlParams.Add(new SqlParameter("@xmrzApprovalStatus", "UNAPPROVED"));

            sqlParams.Add(new SqlParameter("@xmrzName", xmName));
            sqlParams.Add(new SqlParameter("@xmrzProf", xmProf));

            return SqlServerHelper.ExecuteNonQuery(sqlText, sqlParams.ToArray());
        }

        public override SqlDataReader executeDataReader()
        {
            string sqlText = "dbcaac_GetPageOfRecords";
            string columns = @"jyxmId, jyxmName, jyxmProf, CONVERT(VARCHAR(100), jyxmCreateDate, 23) AS jyxmCreateDate,
                               (SELECT nbyhName from [neibuYH] WHERE nbyhId = aTbl.jyxmCreateOwner) AS jyxmCreateOwner,
                               (SELECT jcyzyName from [jcyZY] WHERE jcyzyId = aTbl.jyxmProf) AS jyxmProfChar, 
                                jyxmNewFlag, jyxmDeleteFlag, jyxmLogId";
            string whereText = string.IsNullOrEmpty(xmProf) ? "1=1" : string.Format("jyxmProf={0}", xmProf);

            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@pageSize", pageSize));
            sqlParams.Add(new SqlParameter("@currentPage", pageNumber));
            sqlParams.Add(new SqlParameter("@columns", columns));
            sqlParams.Add(new SqlParameter("@tableName", "jiyuanXM AS aTbl"));
            sqlParams.Add(new SqlParameter("@condition", whereText));
            sqlParams.Add(new SqlParameter("@ascColumn", "jyxmId"));
            sqlParams.Add(new SqlParameter("@bitOrderType", 0));         // 0 是升序, 1 是降序
            sqlParams.Add(new SqlParameter("@pkColumn", "jyxmId"));

            return SqlServerHelper.ExecuteReaderProc(sqlText, sqlParams.ToArray());
        }
    }
}
